/**
 * Copyright (c) 2024,  科泰集团教研团队
 * 版权所有，请勿侵权
 */
package com.ktjiaoyu.news.dao.impl;

import com.ktjiaoyu.news.dao.BaseDao;
import com.ktjiaoyu.news.dao.NewsMemberDao;
import com.ktjiaoyu.news.entity.NewsMember;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 系统会员。游客只能浏览新闻，会员可以发布评论，收藏新闻
 *
 * @author 科泰集团教研团队
 * @version 1.0.0
 * @createTime 2024-4-9 17:36:33
 * @lastModify 2024-4-9 17:36:33
 */
public class NewsMemberDaoImpl extends BaseDao implements NewsMemberDao {

    @Override
    public List<NewsMember> getPage(NewsMember entity, Integer pageNo, Integer pageSize) {
        List<NewsMember> list = new ArrayList<>();
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            List<Object> params = new ArrayList<>();
            String sql = "select * from news_member where 1=1 ";
            // 通过实体创建查询sql
            sql += createPageCondition(entity, params);
            // sql += " order by xxx , xxx desc"
            sql += " limit ?, ?";
            params.add((pageNo - 1) * pageSize);
            params.add(pageSize);
            stmt = conn.prepareStatement(sql);
            for (int i = 0; i < params.size(); i++) {
                stmt.setObject(i + 1, params.get(i));
            }
            rs = stmt.executeQuery();
            while (rs.next()) {
                NewsMember newsMember = new NewsMember();
                newsMember.setId(rs.getLong("id"));
                newsMember.setNickname(rs.getString("nickname"));
                newsMember.setPhone(rs.getString("phone"));
                newsMember.setAvatar(rs.getString("avatar"));
                newsMember.setSex(rs.getInt("sex"));
                newsMember.setRegTime(rs.getDate("regTime"));
                newsMember.setStatus(rs.getInt("status"));
                list.add(newsMember);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll(conn, stmt, rs);
        }
        return list;
    }

    @Override
    public int count(NewsMember entity) {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            List<Object> params = new ArrayList<>();
            String sql = "select count(1) from news_member where 1=1 ";
            // 通过实体创建查询sql
            sql += createPageCondition(entity, params);
            stmt = conn.prepareStatement(sql);
            for (int i = 0; i < params.size(); i++) {
                stmt.setObject(i + 1, params.get(i));
            }
            rs = stmt.executeQuery();
            while (rs.next()) {
                return rs.getInt(1);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll(conn, stmt, rs);
        }
        return 0;
    }

    private String createPageCondition(NewsMember entity, List<Object> params) {
        String sql = "";
        if (entity != null) {
            if (entity.getId() != null) {
                sql += " and id = ?";
                params.add(entity.getId());
            }
            if (entity.getNickname() != null
                    && !entity.getNickname().trim().isEmpty()) {
                sql += " and nickname like concat('%', ? , '%')";
                params.add(entity.getNickname());
            }
            if (entity.getPhone() != null && !entity.getPhone().trim().isEmpty()) {
                sql += " and phone = ?";
                params.add(entity.getPhone());
            }
            if (entity.getSex() != null) {
                sql += " and sex = ?";
                params.add(entity.getSex());
            }
            if (entity.getStatus() != null) {
                sql += " and status = ?";
                params.add(entity.getStatus());
            }
        }
        return sql;
    }

    @Override
    public List<NewsMember> getAll() {
        List<NewsMember> list = new ArrayList<>();
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            List<Object> params = new ArrayList<>();
            String sql = "select * from news_member";
            stmt = conn.prepareStatement(sql);
            rs = stmt.executeQuery();
            while (rs.next()) {
                NewsMember newsMember = new NewsMember();
                newsMember.setId(rs.getLong("id"));
                newsMember.setNickname(rs.getString("nickname"));
                newsMember.setPhone(rs.getString("phone"));
                newsMember.setAvatar(rs.getString("avatar"));
                newsMember.setSex(rs.getInt("sex"));
                newsMember.setRegTime(rs.getDate("regTime"));
                newsMember.setStatus(rs.getInt("status"));
                list.add(newsMember);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll(conn, stmt, rs);
        }
        return list;
    }

    @Override
    public NewsMember getById(Long id) {
        NewsMember newsMember = null;
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            // 获取连接
            conn = getConnection();
            List<Object> params = new ArrayList<>();
            String sql = "select * from news_member where id=?";
            // 获取执行sql对象，设置参数
            stmt = conn.prepareStatement(sql);
            stmt.setObject(1, id);
            // 执行获得结果
            rs = stmt.executeQuery();
            while (rs.next()) {
                newsMember = new NewsMember();
                newsMember.setId(rs.getLong("id"));
                newsMember.setNickname(rs.getString("nickname"));
                newsMember.setPhone(rs.getString("phone"));
                newsMember.setAvatar(rs.getString("avatar"));
                newsMember.setSex(rs.getInt("sex"));
                newsMember.setRegTime(rs.getDate("regTime"));
                newsMember.setStatus(rs.getInt("status"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll(conn, stmt, rs);
        }
        return newsMember;
    }

    @Override
    public int insert(NewsMember entity) {
        return executeUpdate("INSERT INTO news_member(`nickname`,`phone`,`avatar`,`sex`,`regTime`,`status`) VALUES (?,?,?,?,?,?);"
                , entity.getNickname(), entity.getPhone(), entity.getAvatar(), entity.getSex(), entity.getRegTime(), entity.getStatus());
    }

    @Override
    public int[] insertBatch(NewsMember... entities) {
        int[] rows = null;
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            conn.setAutoCommit(false);
            String sql = "INSERT INTO newsMember(`nickname`,`phone`,`avatar`,`sex`,`regTime`,`status`) VALUES (?,?,?,?,?,?);";
            stmt = conn.prepareStatement(sql);
            for (int i = 0; i < entities.length; i++) {
                NewsMember entity = entities[i];
                if (entity == null) {
                    continue;
                }
                int j = 1;
                stmt.setObject(j++, entity.getNickname());
                stmt.setObject(j++, entity.getPhone());
                stmt.setObject(j++, entity.getAvatar());
                stmt.setObject(j++, entity.getSex());
                stmt.setObject(j++, entity.getRegTime());
                stmt.setObject(j++, entity.getStatus());
                stmt.addBatch();
            }
            rows = stmt.executeBatch();
            stmt.clearBatch();
            conn.commit();
        } catch (Exception e) {
            e.printStackTrace();
            if (conn != null) {
                try {
                    conn.rollback();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
        } finally {
            closeAll(conn, stmt, rs);
        }
        return rows;
    }

    @Override
    public int update(NewsMember entity) {
        if(entity == null || entity.getId() == null){
            return 0;
        }
        List<Object> params = new ArrayList<>();
        String sql = "UPDATE `news_member` SET ";
        if(entity.getNickname() != null){
            sql += " `nickname` = ? ,";
            params.add(entity.getNickname());
        }
        if(entity.getPhone() != null){
            sql += " `phone` = ? ,";
            params.add(entity.getPhone());
        }
        if(entity.getAvatar() != null){
            sql += " `avatar` = ? ,";
            params.add(entity.getAvatar());
        }
        if(entity.getSex() != null){
            sql += " `sex` = ? ,";
            params.add(entity.getSex());
        }
        if(entity.getRegTime() != null){
            sql += " `regTime` = ? ,";
            params.add(entity.getRegTime());
        }
        if(entity.getStatus() != null){
            sql += " `status` = ? ,";
            params.add(entity.getStatus());
        }
        if(params.size() > 0){
            // 去除逗号
            sql = sql.substring(0, sql.length() - 1);
            sql += " WHERE id = ? ";
            params.add(entity.getId());
            return executeUpdate(sql, params.toArray());
        }
        return 0;
    }

    @Override
    public int deleteByEntity(NewsMember entity) {
        if (entity == null) {
            return 0;
        }
        List<Object> params = new ArrayList<>();
        String sql = "delete from news_member where 1=1 ";
        if (entity.getId() != null) {
            sql += " and id = ?";
            params.add(entity.getId());
        }
        if (entity.getNickname() != null) {
            sql += " and nickname = ?";
            params.add(entity.getNickname());
        }
        if (entity.getPhone() != null) {
            sql += " and phone = ?";
            params.add(entity.getPhone());
        }
        if (entity.getAvatar() != null) {
            sql += " and avatar = ?";
            params.add(entity.getAvatar());
        }
        if (entity.getSex() != null) {
            sql += " and sex = ?";
            params.add(entity.getSex());
        }
        if (entity.getRegTime() != null) {
            sql += " and regTime = ?";
            params.add(entity.getRegTime());
        }
        if (entity.getStatus() != null) {
            sql += " and status = ?";
            params.add(entity.getStatus());
        }
        if (params.size() == 0) {
            return 0;
        }
        return executeUpdate(sql, params.toArray());
    }

    @Override
    public int deleteById(Long id) {
        return executeUpdate("delete from news_member where id=?", id);
    }

    @Override
    public int[] deleteBatch(Long... ids) {
        int[] rows = null;
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            conn.setAutoCommit(false);
            String sql = "delete from news_member where id=?";
            stmt = conn.prepareStatement(sql);
            for (int i = 0; i < ids.length; i++) {
                Long id = ids[i];
                if (id == null) {
                    continue;
                }
                int j = 1;
                stmt.setObject(j++, id);
                stmt.addBatch();
            }
            rows = stmt.executeBatch();
            stmt.clearBatch();
            conn.commit();
        } catch (Exception e) {
            e.printStackTrace();
            if (conn != null) {
                try {
                    conn.rollback();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
        } finally {
            closeAll(conn, stmt, rs);
        }
        return rows;
    }

}
